﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace FillingDatabaseViaFile
{
    class FillDatabase
    {
        static void Main(string[] args)
        {
            string fileName = "books.txt";
            ReadFileAndFill(fileName);
        }

        public static void ReadFileAndFill(string fileName)
        {
            StreamReader reader = new StreamReader(fileName);
            using (reader)
            {
                string line = reader.ReadLine();
                while (line != null)
                {
                    string[] words = line.Split('|');
                    StringBuilder isbn = new StringBuilder();
                    for (int i = 0; i < words[2].Length; i++)
                    {
                        char currentChar = words[2][i].ToString().ToCharArray()[0];
                        if (char.IsDigit(currentChar))
                        {
                            isbn.Append(currentChar);
                        }
                    }
                    int currentBookID = AddBookToDatabase(
                        words[0].Trim(), isbn.ToString(), decimal.Parse(words[3].Trim()), int.Parse(words[4]), words[5]);
                    string[] authors= words[1].Split(',',';');
                    List<int> authorsIds = new List<int>();

                    foreach (var author  in authors)
                    {
                        int currentAuthorID=AddAuthorToDatabase(author.Trim());
                        authorsIds.Add(currentAuthorID);
                    }
                    foreach (int authorId in authorsIds)
                    {
                        AddBookAuthorToDatabase(currentBookID, authorId);
                    }
                    line = reader.ReadLine();
                }
            }
        }


        public static int AddAuthorToDatabase(string authorName)
        {
            BookstoreDataClassesDataContext context = new BookstoreDataClassesDataContext();
            try
            {
                var existingAuthor =
                    (from a in context.Authors
                     where a.Name == authorName
                     select a).First();
                return existingAuthor.AuthorId;
            }
            catch (InvalidOperationException)
            {
                Author author = new Author()
                {
                    Name = authorName,

                };

                context.Authors.InsertOnSubmit(author);
                context.SubmitChanges();
                return author.AuthorId;
            }




        }

        public static int AddBookToDatabase(string bookTitle,string isbn,decimal price,int amount,string webSite)
        {
            BookstoreDataClassesDataContext context = new BookstoreDataClassesDataContext();
            try
            {
                var existingBook =
                    (from b in context.Books
                     where b.ISBN==isbn
                     select b).First();

                existingBook.Title=bookTitle;
                existingBook.Price=price;
                existingBook.AmountOnHand=existingBook.AmountOnHand+amount;
                existingBook.WebSite = webSite;
                context.SubmitChanges();

                var existingBookAuthors =
                       from ba in context.BooksAuthors
                       where ba.BookId == existingBook.BookId
                       select ba;
                foreach (var author in existingBookAuthors)
                {
                    context.BooksAuthors.DeleteOnSubmit(author);
                    context.SubmitChanges();
                }

                return existingBook.BookId;


            }
            catch (InvalidOperationException)
            {
                Book book = new Book()
                {
                    Title = bookTitle,
                    ISBN = isbn,
                    Price = price,
                    AmountOnHand = amount,
                    WebSite = webSite
                };
                context.Books.InsertOnSubmit(book);
                context.SubmitChanges();
                return book.BookId;
            }

        }
        public static void AddBookAuthorToDatabase(int bookId, int authorId)
        {
            BookstoreDataClassesDataContext context = new BookstoreDataClassesDataContext();

            try
            {
                BooksAuthor booksAuthor = new BooksAuthor()
                    {
                        BookId = bookId,
                        AuthorId = authorId

                    };
                context.BooksAuthors.InsertOnSubmit(booksAuthor);
                context.SubmitChanges();
            }
            catch (Exception)
            {
            }

        }
    }
}
